package dk.muldbjergskolen.db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import dk.muldbjergskolen.model.Inventar;
import dk.muldbjergskolen.model.Klasse;

public class InventarDB
{
	private Connection con;
	
	public InventarDB()
	{
		con = ConnectionDB.getInstance().getDBcon();
	}
	
	/**
	 * finder inventar udfra id
	 * @param inventarId
	 * @return inventar
	 */
	public Inventar getInventar(int inventarId)
	{
		String query = "SELECT id, navn, beskrivelse, antal FROM Inventar WHERE id = " + inventarId;
		Inventar inventar = null;
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			ResultSet rs = stmt.executeQuery(query);
			
			if (rs.next())
			{
				inventar = buildInventar(rs);			
			}
			rs.close();
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		
		return inventar;
	}
	
	/**
	 * Builder inventar
	 * @param result
	 * @return Inventar
	 * @throws SQLException
	 */
	public Inventar buildInventar(ResultSet result) throws SQLException
	{
		Inventar inventar = new Inventar(result.getInt("id"),
					result.getString("navn"),
					result.getString("beskrivelse"),
					result.getInt("antal"));
		
		return inventar;
	}
	
	
	// Finder og returnerer Inventar i en ArrayListe.
	public ArrayList<Inventar> findInventar() {
		ArrayList<Inventar> iK = new ArrayList<Inventar>();
		String query = "Select id, navn, beskrivelse, antal from Inventar";
		Inventar i = null;

		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			ResultSet rs = stmt.executeQuery(query);

			while (rs.next()) {
				i = buildInventar(rs);
				iK.add(i);
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return iK;
	}
	
	
    /**
     * Indsætter Inventar i db.
     * @param i
     */
    public void opretInventar(Inventar i) throws SQLException
    {
    	String query = "insert into Inventar (navn, antal, beskrivelse) values ('"+i.getNavn()+"' , '"+i.getAntal()+"' , '"+i.getBeskrivelse()+"')";
    	
    	try
    	{
    		Statement stmt = con.createStatement();
    		stmt.setQueryTimeout(5);
    		stmt.executeUpdate(query);
    	}
    	catch (SQLException e)
    	{
    		throw e;
    	}
    }

    
    /**
     * updater inventar ud id
     * @param i
     * @param glId
     * @return
     */
	public int updateInventar(Inventar i, int glId) {
		Inventar iObj = i;
		int rc = -1;

		String query = "UPDATE Inventar SET navn='"+iObj.getNavn() +  "' ,beskrivelse ='"+iObj.getBeskrivelse() + "' ,antal ='" +iObj.getAntal() +  "' where id = '" + glId+"'";


		System.out.println("Update query:" + query);
		try { // update Inventar
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
		}// slut try
		catch (Exception e) {
			System.out.println("Update exception in Inventar db: " + e);
		}
		return (rc);
	}
}



